{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 用Python设置Excel数字格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "import datetime\n",
    "\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "ws[\"A1\"] = 1\n",
    "ws[\"A1\"].number_format = 'General'\n",
    "\n",
    "ws[\"A2\"] = 1\n",
    "ws[\"A2\"].number_format = \"0.00\"\n",
    "\n",
    "ws[\"A3\"] = 1\n",
    "ws[\"A3\"].number_format = '0%'\n",
    "\n",
    "ws[\"A4\"] = 1\n",
    "ws[\"A4\"].number_format = '0.00%'\n",
    "\n",
    "ws[\"A5\"] = 100000\n",
    "ws[\"A5\"].number_format = '#,##0'\n",
    "\n",
    "ws[\"A6\"] = 10000000\n",
    "ws[\"A6\"].number_format = '0.00E+00'\n",
    "\n",
    "ws[\"A7\"] = datetime.datetime(2019,6,20,10,30,50)\n",
    "\n",
    "ws[\"A8\"] = datetime.datetime(2019,6,20,10,30,50)\n",
    "ws[\"A8\"].number_format = 'mm-dd-yy'\n",
    "\n",
    "ws[\"A9\"] = datetime.datetime(2019,6,20,10,30,50)\n",
    "ws[\"A9\"].number_format = 'h:mm:ss'\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\number_format.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 用Python设置Excel条件格式"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 突出显示单元格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.formatting.rule import CellIsRule\n",
    "from openpyxl.styles import PatternFill\n",
    "\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "data = [[61,16],\n",
    "        [69,67],\n",
    "        [48,78],\n",
    "        [69,43],\n",
    "        [39,78],\n",
    "        [15,13],\n",
    "        [99,90],\n",
    "        [46,87],\n",
    "        [45,44],\n",
    "        [91,37]]\n",
    "for r in data:\n",
    "    ws.append(r)\n",
    "\n",
    "rule1 =CellIsRule(operator='greaterThan', formula=[50],fill = PatternFill(end_color=\"FFFF2100\"))\n",
    "rule2 =CellIsRule(operator='between', formula=[50,80],fill = PatternFill(end_color=\"FFFF2100\"))\n",
    "    \n",
    "ws.conditional_formatting.add('A1:A10',rule1)\n",
    "ws.conditional_formatting.add('B1:B10',rule2)\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\Standard_conditional.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据条"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [],
   "source": [
    "#针对多行多列数据进行格式设置\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.formatting.rule import DataBarRule\n",
    "\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "data = [[61,16],\n",
    "        [69,67],\n",
    "        [48,78],\n",
    "        [69,43],\n",
    "        [39,78],\n",
    "        [15,13],\n",
    "        [99,90],\n",
    "        [46,87],\n",
    "        [45,44],\n",
    "        [91,37]]\n",
    "for r in data:\n",
    "    ws.append(r)\n",
    "\n",
    "rule = DataBarRule(start_type='min',  end_type='max',\n",
    "                    color=\"FF638EC6\", showValue=True)\n",
    "    \n",
    "ws.conditional_formatting.add('A1:A10',rule)\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\DataBar1.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [],
   "source": [
    "#针对多行多列数据进行格式设置\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.formatting.rule import DataBarRule\n",
    "\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "data = [[61,16],\n",
    "        [69,67],\n",
    "        [48,78],\n",
    "        [69,43],\n",
    "        [39,78],\n",
    "        [15,13],\n",
    "        [99,90],\n",
    "        [46,87],\n",
    "        [45,44],\n",
    "        [91,37]]\n",
    "for r in data:\n",
    "    ws.append(r)\n",
    "\n",
    "rule = DataBarRule(start_type='num', start_value=10, end_type='num', end_value='100',\n",
    "                    color=\"FF638EC6\", showValue=True)\n",
    "    \n",
    "ws.conditional_formatting.add('A1:A10',rule)\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\DataBar2.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 色阶"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.formatting.rule import ColorScaleRule\n",
    "\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "data = [[61,16],\n",
    "        [69,67],\n",
    "        [48,78],\n",
    "        [69,43],\n",
    "        [39,78],\n",
    "        [15,13],\n",
    "        [99,90],\n",
    "        [46,87],\n",
    "        [45,44],\n",
    "        [91,37]]\n",
    "for r in data:\n",
    "    ws.append(r)\n",
    "    \n",
    "ws.conditional_formatting.add('A1:A10',\n",
    "             ColorScaleRule(start_type='min', start_color='FFFFF0F5',\n",
    "                           end_type='max', end_color='FFFF7F00')\n",
    "                          )\n",
    "\n",
    "ws.conditional_formatting.add('B1:B10',\n",
    "                ColorScaleRule(start_type='percentile', start_value=10, start_color='FFFFE4E1',\n",
    "                            mid_type='percentile', mid_value=50, mid_color='FFFFC1C1',\n",
    "                            end_type='percentile', end_value=90, end_color='FFFF3030')\n",
    "                              )\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\ColorScale.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 图标集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.formatting.rule import IconSetRule\n",
    "from openpyxl.formatting.rule import ColorScaleRule\n",
    "\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "data = [[61,16],\n",
    "        [69,67],\n",
    "        [48,78],\n",
    "        [69,43],\n",
    "        [39,78],\n",
    "        [15,13],\n",
    "        [99,90],\n",
    "        [46,87],\n",
    "        [45,44],\n",
    "        [91,37]]\n",
    "for r in data:\n",
    "    ws.append(r)\n",
    "    \n",
    "\n",
    "rule1 = IconSetRule('3Flags', 'percent', [0,30,50], showValue=True, reverse=False)\n",
    "rule2 = IconSetRule('3Arrows', 'percent', [0,30,50], showValue=False, reverse=False)\n",
    "\n",
    "ws.conditional_formatting.add('A1:A10',rule1)\n",
    "ws.conditional_formatting.add('B1:B10',rule2)\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\IconSet.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "789px",
    "left": "69px",
    "top": "110px",
    "width": "308.507px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
